#include "sqlcontroller.h"
#include "config.h"


#include <QDebug>
#include <QSqlQuery>
#include <QSqlError>
#include <QSqlQueryModel>
#include <QSqlRelation>


SqlController *SqlController::s_instance=nullptr;
SqlController::SqlController()
    :m_prodectNum(0)
    ,name()
{

}

SqlController *SqlController::getSqlController()
{
    if(s_instance==nullptr){
        s_instance=new SqlController();
    }
    return s_instance;
}

bool SqlController::initDataBase()
{
    m_db=QSqlDatabase::addDatabase(DB_DRIVE);
    m_db.setHostName(HOST_NAME);
    m_db.setPort(DB_PORT);
    m_db.setUserName(DB_USER_NAME);
    m_db.setPassword(DB_PASSWORD);
    m_db.setDatabaseName(DB_DATABASE_NAME);
    if(m_db.open()){
        qDebug()<<"连接成功";
        return true;
    }else{
        qDebug()<<"连接失败";
        return false;
    }
}
//查询用户表是否有该用户
bool SqlController::removeUser(QString name)
{
    QString strUser("select count(*) from user where username=?");
    QSqlQuery sqlUser;
    sqlUser.prepare(strUser);
    sqlUser.addBindValue(name);
    if(sqlUser.exec())
    {
        while (sqlUser.next()) {
            if(sqlUser.value(0).toInt()==0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        return false;
    }
    else
    {
        qDebug()<<"error:"<<sqlUser.lastError().text();
        return false;
    }
}
//加密
QString SqlController::strToMd5(QString str)
{
    QString strMd5;
    QByteArray qba;
    QString befStr = str;
    qDebug() << "befStr =>" << befStr;
    qba = QCryptographicHash::hash(befStr.toLatin1(), QCryptographicHash::Md5);
    qba = QCryptographicHash::hash(qba, QCryptographicHash::Md5);
    strMd5.append(qba.toHex());
    qDebug()<<"strMd5"<<strMd5;
    return strMd5;
}

int SqlController::prodectNum() const
{
    return m_prodectNum;
}

void SqlController::setProdectNum(int newProdectNum)
{
    m_prodectNum = newProdectNum;
}

const ProductInfo &SqlController::getTempPro() const
{
    return tempPro;
}

void SqlController::setTempPro(const ProductInfo &newTempPro)
{
    tempPro = newTempPro;
}

void SqlController::setName(QString str)
{
    name = str;
}

int SqlController::getUser_id() const
{
    return user_id;
}

void SqlController::setUser_id(int newUser_id)
{
    user_id = newUser_id;
}

//注册
bool SqlController::goRegister(QString name, QString pwd, int auth)
{
    //判断要注册的用户是否存在
    if(!removeUser(name)){
        qDebug()<<"该用户存在";
        return false;
    }
    //注册
    QString strReg("insert into user(username,password,auth) values(?,?,?)");
    QSqlQuery sqlReg;
    //加密
    pwd = strToMd5(pwd);
    sqlReg.prepare(strReg);
    sqlReg.addBindValue(name);
    sqlReg.addBindValue(pwd);
    sqlReg.addBindValue(auth);
    if(sqlReg.exec()){
        return true;
    }else{
        qDebug()<<"error:"<<sqlReg.lastError().text();
        return false;
    }
}
//登录
bool SqlController::goLogin(QString name, QString pwd,int auth)
{
    QString strLog("select * from user where username=? and auth=?");
    QSqlQuery sqlLogin;
    //加密之后再判断
    pwd = strToMd5(pwd);
    sqlLogin.prepare(strLog);
    sqlLogin.addBindValue(name);
    sqlLogin.addBindValue(auth);

    if(sqlLogin.exec()){
        while (sqlLogin.next()) {
            qDebug()<<"当前用户id："<<sqlLogin.value("id").toInt();
            setUser_id(sqlLogin.value("id").toInt());
            QString password=sqlLogin.value("password").toString();
            if(pwd==password){
                return true;
            }else{
                return false;
            }
        }
        return false;
    }
    else{
        qDebug()<<"error:"<<sqlLogin.lastError().text();
        return false;
    }
}


bool SqlController::removeUser(int id)
{
    QString str = QString("DELETE FROM user where id = %1").arg(id);
     QSqlQuery sql_Delete;
     if(sql_Delete.exec(str)){
         return true;
     } else return false;
}



//视图
QSqlRelationalTableModel* SqlController::getModel()
{

    QSqlRelationalTableModel *model = new QSqlRelationalTableModel();//关联模型
    model->setTable("product");//主表
    model->setRelation(9,QSqlRelation("user","id","username"));//将第10列与user表中的id关联显示suer中对应的username.
    model->select();//执行查询

    //给字段添加台头
    model->setHeaderData(0,Qt::Horizontal,"id");
    model->setHeaderData(1,Qt::Horizontal,"商品名称");
    model->setHeaderData(2,Qt::Horizontal,"数量");
    model->setHeaderData(3,Qt::Horizontal,"分类");
    model->setHeaderData(4,Qt::Horizontal,"价格");
    model->setHeaderData(5,Qt::Horizontal,"重量");
    model->setHeaderData(6,Qt::Horizontal,"供应商");
    model->setHeaderData(7,Qt::Horizontal,"创建时间");
    model->setHeaderData(8,Qt::Horizontal,"修改时间");
    model->setHeaderData(9,Qt::Horizontal,"操作员");

    return model;

}


QSqlTableModel* SqlController::getModelUserManage(){

    QSqlTableModel *model = new QSqlTableModel();
    model->setTable("user");
    model->select();
    model->setEditStrategy(QSqlTableModel::OnManualSubmit);
    model->setHeaderData(0, Qt::Horizontal, "ID");
    model->setHeaderData(1, Qt::Horizontal, "姓名");
    model->setHeaderData(2, Qt::Horizontal, "密码");
    model->setHeaderData(3, Qt::Horizontal, "角色");
    return model;

}

bool SqlController::addproduct(ProductInfo pro)
{
    QString strPro("insert into product(name,nums,classify,price,weight,supplier,user_id) value(?,?,?,?,?,?,?)");
    QSqlQuery sqlPro;
    sqlPro.prepare(strPro);
    sqlPro.addBindValue(pro.name);
    sqlPro.addBindValue(pro.nums);
    sqlPro.addBindValue(pro.classify);
    sqlPro.addBindValue(pro.price);
    sqlPro.addBindValue(pro.weight);
    sqlPro.addBindValue(pro.supplier);
    sqlPro.addBindValue(pro.user_id);
    if(sqlPro.exec()){
        qDebug()<<"insert success";
        return true;
    }else{
        qDebug()<<"insert failed";
        return false;
    }
}
//出库
void SqlController::outletnum(int id, int num)
{
    QSqlQuery query ;
    QString str  = QString("update product set nums = %1 ,user_id = %2 where id = %3").arg(num).arg(SqlController::getSqlController()->getUser_id()).arg(id);
    qDebug() << "str:" << str;
    qDebug()<<"num and id "<<num<<" "<<id;
    if(query.exec(str))
    {
        qDebug()<<"outlet success";
    }
    else
    {
        qDebug()<<"outlet failed";
    }
}

bool SqlController::updateProduct(ProductInfo pro, int id)
{
    QString strUpdatePro("update product set name=?,nums=?,classify=?,price=?,weight=?,supplier=?,user_id=? where id=?");
    QSqlQuery sqlUpdatePro;
    sqlUpdatePro.prepare(strUpdatePro);
    sqlUpdatePro.addBindValue(pro.name);
    sqlUpdatePro.addBindValue(pro.nums);
    sqlUpdatePro.addBindValue(pro.classify);
    sqlUpdatePro.addBindValue(pro.price);
    sqlUpdatePro.addBindValue(pro.weight);
    sqlUpdatePro.addBindValue(pro.supplier);
    sqlUpdatePro.addBindValue(pro.user_id);
    sqlUpdatePro.addBindValue(id);
    if(sqlUpdatePro.exec()){
        qDebug()<<"修改成功";
        return true;
    }else{
        qDebug()<<sqlUpdatePro.lastError().text();
        return false;
    }
}

//查询
QSqlRelationalTableModel* SqlController::selectProduct()
{
    QSqlRelationalTableModel *model = new QSqlRelationalTableModel();//关联模型

    model->setTable("product");//主表
//    model->setRelation(9,QSqlRelation("user","id","username"));//将第10列与user表中的id关联显示suer中对应的username.
    model->select();//执行查询
    QString strFilter = QString("name like '%%1%'").arg(name);//过滤条件
    qDebug() << "strFilter:" << strFilter;
    model->setFilter(strFilter);
    qDebug() << "model->filter():" << model->filter();

    //给字段添加台头
    model->setHeaderData(0,Qt::Horizontal,"id");
    model->setHeaderData(1,Qt::Horizontal,"商品名称");
    model->setHeaderData(2,Qt::Horizontal,"数量");
    model->setHeaderData(3,Qt::Horizontal,"分类");
    model->setHeaderData(4,Qt::Horizontal,"价格");
    model->setHeaderData(5,Qt::Horizontal,"重量");
    model->setHeaderData(6,Qt::Horizontal,"供应商");
    model->setHeaderData(7,Qt::Horizontal,"创建时间");
    model->setHeaderData(8,Qt::Horizontal,"修改时间");
    qDebug()<<"str: "<<model->lastError().text();
    name = "/n";

    return model;

}






